#基差&年化基差成本计算（直接采用数据库中的交割日）

import numpy as np
import pymysql
import pandas as pd
import calendar
import datetime
import math

# 得到主力合约映射表
def get_futures_contract_mapping(code):
    wind_engine = pymysql.connect(host='192.168.201.181', port=3306, user='huisheng_hzl', passwd='windreader_hzlAA32',
                                  db='wind')  # 上海
    sql = f"select S_INFO_WINDCODE,FS_MAPPING_WINDCODE,STARTDATE,ENDDATE from CfuturesContractMapping  where S_INFO_WINDCODE = '{code}'"
    data = pd.read_sql(sql, wind_engine)
    data = data.sort_values("ENDDATE")
    data = data.dropna()
    return data


def tradeDays(front, latter, tD):
    front = pd.to_datetime(front)
    latter = pd.to_datetime(latter)
    tD = tD.loc[front:latter]
    tradeDay = len(tD) - 1
    return tradeDay

#获取期货数据
start_date = '20190101'
end_date = '20250120'
contract = 'IC'
#benchmark = '000852.SH'
benchmark = '000905.SH'

#获取指数数据
start = pd.to_datetime(start_date)
end = pd.to_datetime(end_date)

wind_engine = pymysql.connect(host='192.168.201.181', port=3306, user='huisheng_hzl', passwd='windreader_hzlAA32',
                            db='wind')  # 上海

#获取历史和未来的交易日期数据，以备后续计算交易日间隔
sql_str = 'SELECT TRADE_DAYS, S_INFO_EXCHMARKET' + ' FROM ' + 'AShareCalendar' + ' WHERE ' + 'S_INFO_EXCHMARKET = "SSE"'
tradeD = pd.read_sql(sql_str, wind_engine)
tradeD['TRADE_DAYS'] = pd.to_datetime(tradeD['TRADE_DAYS'])
tradeD.sort_values(by='TRADE_DAYS', ascending=True, inplace=True)
tradeD = tradeD.set_index('TRADE_DAYS')

#获取交割日
deliveryList = get_futures_contract_mapping(f'{contract}00.CFE')['ENDDATE']

#获取最新次月合约交割日
deliveryList_01 = get_futures_contract_mapping(f'{contract}01.CFE')['ENDDATE']
nowDelivery = deliveryList_01.iloc[-1]

#获取当月连续合约
sql_str0 = 'SELECT S_INFO_WINDCODE, TRADE_DT, S_DQ_SETTLE, FS_INFO_TYPE' + ' FROM ' + 'CIndexFuturesEODPrices' + \
          ' WHERE ' + 'FS_INFO_TYPE = 3 ' + 'AND ' + f"S_INFO_WINDCODE = '{contract}00.CFE' " + 'AND ' + 'TRADE_DT > CAST({} as DATE)'.format(start_date)

#获取次月连续合约
sql_str1 = 'SELECT S_INFO_WINDCODE, TRADE_DT, S_DQ_SETTLE, FS_INFO_TYPE' + ' FROM ' + 'CIndexFuturesEODPrices' + \
          ' WHERE ' + 'FS_INFO_TYPE = 3 ' + 'AND ' + f"S_INFO_WINDCODE = '{contract}01.CFE' " + 'AND ' + 'TRADE_DT > CAST({} as DATE)'.format(start_date)

settlement_price1 = pd.read_sql(sql_str1, wind_engine)
settlement_price1.set_index('TRADE_DT', inplace=True)
settlement_price1.index = pd.to_datetime(settlement_price1.index)
settlement_price1 = settlement_price1[['S_DQ_SETTLE']]

#获取指数数据
close = pd.read_csv(
    f'http://dataway.hhhstz.com/hsic_base_fmt/cube?tableName=b_stocka_indexmarketday&begDate={start.strftime("%Y-%m-%d")}&endDate={end.strftime("%Y-%m-%d")}&fields=index,n_close&c_indexCode={benchmark}')
close = close.set_index('index')
close.index = pd.to_datetime(close.index)
close = close.sort_index()

basisR = pd.merge(settlement_price1, close, left_index=True, right_index=True, how='inner')
basisR.sort_index(ascending=True, inplace=True)
basisR.columns = ['settlePrice', 'indexClose']

#次月合约基差
basisR['基差'] = basisR['settlePrice']/basisR['indexClose'] - 1


basisYear = {}
dList = []
for i in basisR.index:
    #获取交割日
    deliveryD = ''
    for j in range(0, len(deliveryList)):
        if (pd.to_datetime(i) - pd.to_datetime(deliveryList.iloc[j])).days <= 0:
            if j == len(deliveryList) - 1:   #最新次月合约交割日
                deliveryD = nowDelivery
            else:
                deliveryD = deliveryList.iloc[j+1]
                break
    if deliveryD == '':
        deliveryD = nowDelivery
    #计算年化基差成本
    # 按照交易日计算年化
    # leftTradeDay = tradeDays(i, deliveryD, tradeD)
    # dList.append(deliveryD)
    # basisYear[i] = basisR.loc[i, '基差'] / leftTradeDay * 250

    # 按照自然日计算年化
    leftTradeDay = (pd.to_datetime(deliveryD) - pd.to_datetime(i)).days
    dList.append(deliveryD)
    basisYear[i] = basisR.loc[i, '基差'] / leftTradeDay * 365


basisYear = pd.Series(basisYear)
basisYear = basisYear.to_frame()
basisYear.columns = ['年化基差成本']

basisR = pd.merge(basisR, basisYear, left_index=True, right_index=True, how='left')
basisR['交割日'] = dList

basisR.to_excel('./次月合约' + contract + '基差&年化基差成本.xlsx')